﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MessageBuffer_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MessageBuffer_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[MessageBuffer_InsertUpdate]
	@bufferId nvarchar(255),
    @messageValidityDurationSecs bigint,
    @maxMessageCount bigint = null,
    @returnCode int output,
    @bufferDbId bigint output
AS 
    BEGIN
        SET NOCOUNT ON;

		SET @returnCode = 0
        BEGIN TRY
			UPDATE MessageBuffer
			SET
				Id = @bufferId,
				MessageValidityDurationSecs = @messageValidityDurationSecs,
				MaxMessageCount = @maxMessageCount
			WHERE Id = @bufferId

			IF @@ROWCOUNT = 1
			BEGIN
				SET @bufferDbId =
				(
					SELECT DbId
					FROM MessageBuffer WITH (NOLOCK)
					WHERE
						Id = @bufferId
				)
			END
			ELSE IF @@ROWCOUNT = 0
			BEGIN
				INSERT INTO MessageBuffer
				(
					Id,
					MessageValidityDurationSecs,
					MaxMessageCount
				)
				VALUES
				(
					@bufferId,
					@messageValidityDurationSecs,
					@maxMessageCount
				)
				SET @bufferDbId = @@IDENTITY
			END
			
		END TRY
		BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
	   END CATCH
    END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateMessage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreateMessage]
GO
CREATE PROCEDURE [dbo].[CreateMessage]
	@correlationId nvarchar(100),
    @action nvarchar(255),
    @receiveTime datetime2,
    @receiveAddress nvarchar(max),
    @returnCode int output,
    @messageId bigint output
AS 
    BEGIN
        SET NOCOUNT ON;

        DECLARE @currentTime datetime2(7) = SYSUTCDATETIME()

		SET @returnCode = 0
        BEGIN TRY
            INSERT INTO Message
			(
				CorrelationId,
				Action,
				ReceiveTime,
				ReceiveAddress,
				CreateTime
			)
            VALUES
			(
				@correlationId,
				@action,
				@receiveTime,
				@receiveAddress,
				@currentTime
			)
			SET @messageId = SCOPE_IDENTITY()
			
		END TRY
		BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
	   END CATCH
    END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RollbackEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RollbackEntry]
GO
CREATE PROCEDURE [dbo].[RollbackEntry]
    @queueEntryId int,
    @errorMsg nvarchar(max),
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

		SET @returnCode = 0
        BEGIN TRY
            UPDATE QueueEntry
            WITH (ROWLOCK)
            SET
				StateId = 1,
				MachineName = NULL,
				ProcessId = NULL,
				ProcessorId = NULL,
				BatchId = NULL,
				DeliveryAttemptCount = DeliveryAttemptCount + 1,
				DeliveryTime = NULL,
				ErrorMessage = @errorMsg
			WHERE
				Id = @queueEntryId

        END TRY
        BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
       END CATCH
    END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompleteMessage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CompleteMessage]
GO
CREATE PROCEDURE [dbo].[CompleteMessage]
    @messageId int,
    @partCount int,
    @size int,
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

		SET @returnCode = 0
 
		BEGIN TRY
			UPDATE Message
			WITH (UPDLOCK)
			SET
				PartCount = @partCount,
				Size = @size
			WHERE
				Id = @messageId

			UPDATE QueueEntry
			WITH (UPDLOCK)
			SET
				StateId = 1
			WHERE
				MessageId = @messageId AND
				StateId = 0
				
		END TRY
		BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
	   END CATCH
    END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompleteEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CompleteEntry]
GO
CREATE PROCEDURE [dbo].[CompleteEntry]
    @queueEntryId int,
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

		SET @returnCode = 0
        BEGIN TRY
            UPDATE QueueEntry
            WITH (UPDLOCK)
            SET
				StateId = 3,
				DeliveryAttemptCount = DeliveryAttemptCount + 1,
				DeliveryTime = SYSUTCDATETIME()
			WHERE
				Id = @queueEntryId

        END TRY
        BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
       END CATCH
    END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CleanQueueEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CleanQueueEntry]
GO
CREATE PROCEDURE [dbo].[CleanQueueEntry]
    @returnCode int output,
    @count int output
AS 
    BEGIN
        SET NOCOUNT ON;

		SET @returnCode = 0
		
		DECLARE @EntriesToBeDeletedTable TABLE(
			[Id] bigint PRIMARY KEY,
			[MessageId] bigint);

		BEGIN TRY
			INSERT INTO @EntriesToBeDeletedTable
			SELECT TOP (100)
				Id,
				MessageId
			FROM QueueEntry WITH (NOLOCK)
			WHERE
				StateId >= 3 OR
				ExpirationTime < SYSUTCDATETIME()
					
			DELETE FROM MessagePart WITH (ROWLOCK)
			WHERE MessageId IN
				(SELECT MessageId FROM @EntriesToBeDeletedTable)
			
			DELETE FROM Message WITH (ROWLOCK)
			WHERE Id IN
				(SELECT MessageId FROM @EntriesToBeDeletedTable)

			DELETE FROM QueueEntry WITH (ROWLOCK)
			WHERE Id IN
				(SELECT Id FROM @EntriesToBeDeletedTable)

		END TRY
		BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
	   END CATCH
    END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BufferMessage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[BufferMessage]
GO
CREATE PROCEDURE [dbo].[BufferMessage]
	@bufferId nvarchar(255),
	@messageId bigint,
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;
        
        DECLARE @bufferDbId bigint
        DECLARE @sequenceNumber bigint
        DECLARE @messageValidityDurationSecs bigint
        DECLARE @maxMessageCount bigint
        
        UPDATE MessageBuffer WITH (UPDLOCK)
        SET
			SequenceNumber += 1,
			@bufferDbId = DbId,
			@sequenceNumber = SequenceNumber + 1,
			@messageValidityDurationSecs = MessageValidityDurationSecs,
			@maxMessageCount = MaxMessageCount
		WHERE
			Id = @bufferId
        
        IF @bufferDbId is null
        BEGIN
			SET @returnCode = 1
			RAISERROR(N'MessageBuffer does not exist.',10,1)
	    END
					   
        DECLARE @currentTime datetime2(7) = SYSUTCDATETIME()
        
        DECLARE @messageCount bigint
        SELECT @messageCount = COUNT(BufferDbId)
        FROM MessageBuffer_Message WITH (NOLOCK)
        WHERE
			BufferDbId = @bufferDbId AND
			ExpirationTime > @currentTime
		
		IF @messageCount > @maxMessageCount
		BEGIN
			SET @returnCode = 1
			RAISERROR(N'MessageBuffer if full.',10,1)
		END
        
        DECLARE @expirationTime datetime2(7) = DATEADD(second, @messageValidityDurationSecs, @currentTime)

		SET @returnCode = 0
        BEGIN TRY
            INSERT INTO MessageBuffer_Message
			(
				BufferDbId,
				MessageId,
				InsertTime,
				ExpirationTime,
				SequenceNumber
			)
            VALUES
			(
				@bufferDbId,
				@messageId,
				@currentTime,
				@expirationTime,
				@sequenceNumber
			)

        END TRY
        BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
       END CATCH
    END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QueryMessageBuffer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[QueryMessageBuffer]
GO
CREATE PROCEDURE [dbo].[QueryMessageBuffer]
	@bufferId nvarchar(255),
	@maxResultCount bigint = null,
	@minTime datetime2(7) = null,
	@minSequenceNumber bigint = null
AS
BEGIN
    BEGIN TRY
		DECLARE @bufferDbId bigint
		DECLARE @messageValidityDurationSecs bigint
		DECLARE @maxMessageCount bigint
	    
		SELECT
			@bufferDbId = DbId,
			@messageValidityDurationSecs = MessageValidityDurationSecs,
			@maxMessageCount = MaxMessageCount
		FROM MessageBuffer WITH (NOLOCK)
		WHERE
			Id = @bufferId

		DECLARE @maxCount bigint = @maxResultCount
		IF @maxCount is null
		BEGIN
			SET @maxCount = @maxMessageCount
		END

		DECLARE @currentTime datetime2(7) = SYSUTCDATETIME()

		SELECT TOP (@maxCount)
			MessageId
		FROM MessageBuffer_Message
		WHERE
			BufferDbId = @bufferDbId AND
			ExpirationTime > @currentTime AND
			(@minTime is null OR InsertTime >= @minTime) AND
			(@minSequenceNumber is null OR SequenceNumber >= @minSequenceNumber)

	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT @ErrorMessage = ERROR_MESSAGE(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE();

		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMessagePart]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetMessagePart]
GO
CREATE PROCEDURE [dbo].[GetMessagePart]
	@messageId bigint,
	@partIndex int
AS
BEGIN
    SET NOCOUNT ON;

	BEGIN TRY
		SELECT
			MessageId,
			PartIndex,
			Body,
			Size
		FROM MessagePart WITH (NOLOCK)
		WHERE
			MessageId = @messageId AND
			PartIndex = @partIndex
    END TRY
    BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT @ErrorMessage = ERROR_MESSAGE(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE();

		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   );
   END CATCH
	
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FaultEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[FaultEntry]
GO
CREATE PROCEDURE [dbo].[FaultEntry]
    @queueEntryId int,
    @errorMsg nvarchar(max),
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

		SET @returnCode = 0
        BEGIN TRY
            UPDATE QueueEntry
            WITH (UPDLOCK)
            SET
				StateId = 4,
				--MachineName = NULL,
				--ProcessId = NULL,
				--ProcessorId = NULL,
				BatchId = NULL,
				DeliveryAttemptCount = DeliveryAttemptCount + 1,
				DeliveryTime = NULL,
				ErrorMessage = @errorMsg
			WHERE
				Id = @queueEntryId

        END TRY
        BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
       END CATCH
    END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EnqueueEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[EnqueueEntry]
GO
CREATE PROCEDURE [dbo].[EnqueueEntry]
	@messageId bigint,
	@routingHeader varbinary(max),
	@priority int,
	@orderedDelivery bit,
	@receiverMachineName nvarchar(255),
	@receiverProcessId int,
	@receiverAppDomainId int,
    @messageValidityDurationSecs bigint = NULL,
    @deliveryAttemptIntervalSecs bigint,
    @maxDeliveryAttemptCount int,
    @isResponse bit,
    @returnCode int output,
    @queueEntryId int output
AS 
    BEGIN
        SET NOCOUNT ON;
        SET DEADLOCK_PRIORITY HIGH
        
        DECLARE @currentTime datetime2(7) = SYSUTCDATETIME()
        DECLARE @expirationTime datetime2(7) = DATEADD(second, @messageValidityDurationSecs, @currentTime)

		SET @returnCode = 0
        BEGIN TRY
            INSERT INTO QueueEntry
			(
				MessageId,
				RoutingHeader,
				Priority,
				OrderedDelivery,
				ReceiverMachineName,
				ReceiverProcessId,
				ReceiverAppDomainId,
				EnqueueTime,
				ExpirationTime,
				StateId,
				DequeueAttemptCount,
				DeliveryAttemptCount,
				DeliveryAttemptIntervalSecs,
				MaxDeliveryAttemptCount,
				IsResponse
			)
            VALUES
			(
				@messageId,
				@routingHeader,
				@priority,
				@orderedDelivery,
				@receiverMachineName,
				@receiverProcessId,
				@receiverAppDomainId,
				@currentTime,
				@expirationTime,
				0,
				0,
				0,
				@deliveryAttemptIntervalSecs,
				@maxDeliveryAttemptCount,
				@isResponse
			)
			SET @queueEntryId = SCOPE_IDENTITY()

        END TRY
        BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
       END CATCH
    END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DequeueEntries]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DequeueEntries]
GO
CREATE PROCEDURE [dbo].[DequeueEntries]
    @count int,
    @machineName nvarchar(100),
    @processId int,
    @appDomainId int,
    @processorId nvarchar(100),
    @returnCode int output,
    @batchId uniqueidentifier output
AS 
    BEGIN
        SET NOCOUNT ON;
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED

        DECLARE @currentTime datetime2
        SET @currentTime = SYSUTCDATETIME()
        SET @batchId = NEWID()
        SET @returnCode = 0
        BEGIN TRY
            BEGIN TRAN T1
            
            UPDATE TOP(@count) QueueEntry
            -- WITH (ROWLOCK, XLOCK, HOLDLOCK)
            WITH (UPDLOCK, ROWLOCK, READPAST)
            SET
                StateId = 2,
                MachineName = @machineName,
                ProcessId = @processId,
                ProcessorId = @processorId,
                BatchId = @batchId,
                DequeueTime = @currentTime,
                DequeueAttemptCount = DequeueAttemptCount + 1
                WHERE
                    StateId = 1 AND
                    BatchId IS NULL AND
                    (ExpirationTime IS NULL OR
                     ExpirationTime < @currentTime) AND                   
                    ((IsResponse = 0) OR
                     (IsResponse = 1 AND
                      ReceiverMachineName = @machineName AND
                      ReceiverProcessId = @processId AND
                      ReceiverAppDomainId = @appDomainId))

            SELECT
                qe.[Id],
                qe.MessageId,
                qe.RoutingHeader,
                qe.Priority,
                qe.OrderedDelivery,
                qe.ReceiverMachineName,
                qe.ReceiverProcessId,
                qe.ReceiverAppDomainId,
                qe.EnqueueTime,
                qe.ExpirationTime,
                qe.StateId,
                qe.MachineName,
                qe.ProcessId,
                qe.ProcessorId,
                qe.BatchId,
                qe.DequeueTime,
                qe.DequeueAttemptCount,
                qe.DeliveryAttemptCount,
                qe.IsResponse,
                m.PartCount,
                m.Size,
                m.CorrelationId,
                m.Action,
                m.ReceiveTime,
                m.ReceiveAddress,
                m.CreateTime
            FROM QueueEntry qe WITH (NOLOCK), Message m WITH (NOLOCK)
            WHERE
                qe.BatchId = @batchId AND
                qe.MessageId = m.Id
            ORDER BY
                [Priority] DESC, [Id] ASC

            COMMIT TRAN T1
                
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRAN T1

            SET @batchId = NULL
            SET @returnCode = 1

            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
       END CATCH
    END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteMessage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteMessage]
GO
CREATE PROCEDURE [dbo].[DeleteMessage]
	@messageId bigint,
    @returnCode int output
AS 
    BEGIN
        SET NOCOUNT ON;

		SET @returnCode = 0
        BEGIN TRY
			DELETE FROM MessagePart WITH (ROWLOCK)
			WHERE MessageId = @messageId
			
			DELETE FROM Message WITH (ROWLOCK)
			WHERE Id = @messageId
		END TRY
		BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
	   END CATCH
    END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateMessagePart]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreateMessagePart]
GO
CREATE PROCEDURE [dbo].[CreateMessagePart]
	@messageId bigint,
	@partIndex int,
    @body varbinary(max),
    @size int,
    @returnCode int output,
    @messagePartId int output
AS 
    BEGIN
        SET NOCOUNT ON;

		SET @returnCode = 0
        BEGIN TRY
            INSERT INTO MessagePart
			(
				MessageId,
				PartIndex,
				Body,
				Size
			)
            VALUES
			(
				@messageId,
				@partIndex,
				@body,
				@size
			)
			SET @messagePartId = SCOPE_IDENTITY()
		END TRY
		BEGIN CATCH
			SET @returnCode = 1

			DECLARE @ErrorMessage NVARCHAR(4000);
			DECLARE @ErrorSeverity INT;
			DECLARE @ErrorState INT;

			SELECT @ErrorMessage = ERROR_MESSAGE(),
				   @ErrorSeverity = ERROR_SEVERITY(),
				   @ErrorState = ERROR_STATE();

			RAISERROR (@ErrorMessage, -- Message text.
					   @ErrorSeverity, -- Severity.
					   @ErrorState -- State.
					   );
	   END CATCH
    END
GO